from SqlBase_v3_15 import DBClient, sql_command
from datetime import datetime
import os
import sqlite3

class ContractDatabase:
    def __init__(self):
        # 确保数据库目录存在
        db_dir = 'database'
        if not os.path.exists(db_dir):
            os.makedirs(db_dir)
        
        # 设置数据库文件路径
        self.db_path = os.path.join(db_dir, 'contracts.db')
        
        # 初始化数据库连接
        self._init_db()
        
        # 创建 SQL 命令生成器
        self.sql_cmd = sql_command('sqlite')
        self._init_tables()

    def _init_db(self):
        """初始化数据库连接"""
        connection = sqlite3.connect(self.db_path, check_same_thread=False)
        connection.isolation_level = None  # 启用自动提交
        self.db = DBClient(
            mode='sqlite',
            debug=True,
            connection=connection
        )
        self.connection = connection

    def __del__(self):
        """析构函数，确保数据库连接被正确关闭"""
        if hasattr(self, 'connection'):
            self.connection.close()

    def _init_tables(self):
        """初始化数据库表"""
        # 创建合同表
        sql_list = self.sql_cmd.table_get()
        result = self.db.send_to_server(sql_list)
        if result and result[0]['status'] == 'success':
            existing_tables = [table['name'] for table in result[0]['data']] if result[0]['data'] else []
        else:
            existing_tables = []
        
        if 'default_table' not in existing_tables:
            # 创建合同表
            sql_list = self.sql_cmd.table_add(['default_table'])
            self.db.send_to_server(sql_list)
            columns = {
                'pro_name': 'TEXT',      # 项目名称
                'my_comma': 'TEXT',      # 我方公司
                'ven_name': 'TEXT',      # 供应商名称
                'unique_id': 'TEXT',     # 唯一编号
                'item_sup': 'TEXT',      # 供应物品
                'people': 'TEXT',        # 负责人
                'money': 'REAL',         # 金额
                'date': 'TEXT',          # 日期
                'cate': 'TEXT',          # 类别（对公/对私）
                'status': 'TEXT',        # 状态
                'note': 'TEXT'           # 备注
            }
            sql_list = self.sql_cmd.column_add('default_table', columns)
            self.db.send_to_server(sql_list)

        if 'approval_records' not in existing_tables:
            # 创建审批记录表
            sql_list = self.sql_cmd.table_add(['approval_records'])
            self.db.send_to_server(sql_list)
            columns = {
                'contract_id': 'INTEGER',  # 合同ID
                'approver': 'TEXT',        # 审批人
                'status': 'TEXT',          # 状态
                'comment': 'TEXT',         # 审批意见
                'created_at': 'TIMESTAMP'  # 创建时间
            }
            sql_list = self.sql_cmd.column_add('approval_records', columns)
            self.db.send_to_server(sql_list)

        if 'contract_files' not in existing_tables:
            # 创建合同文件表
            sql_list = self.sql_cmd.table_add(['contract_files'])
            self.db.send_to_server(sql_list)
            columns = {
                'contract_id': 'INTEGER',   # 合同ID
                'filename': 'TEXT',         # 文件名
                'file_type': 'TEXT',        # 文件类型
                'is_converted': 'BOOLEAN',  # 是否已转换
                'created_at': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'  # 创建时间
            }
            sql_list = self.sql_cmd.column_add('contract_files', columns)
            self.db.send_to_server(sql_list)

    def create_contract(self, contract_data):
        """创建新合同
        contract_data 包含：
        - pro_name: 项目名称
        - my_comma: 我方公司
        - ven_name: 供应商名称
        - unique_id: 唯一编号
        - item_sup: 供应物品
        - people: 负责人
        - money: 金额
        - date: 日期
        - cate: 类别（对公/对私）
        - status: 状态
        - note: 备注
        """
        try:
            data = {
                'pro_name': contract_data[0],
                'my_comma': contract_data[1],
                'ven_name': contract_data[2],
                'unique_id': contract_data[3],
                'item_sup': contract_data[4],
                'people': contract_data[5],
                'money': contract_data[6],
                'date': contract_data[7],
                'cate': contract_data[8],
                'status': contract_data[9],
                'note': contract_data[10]
            }
            print("准备插入数据:", data)  # 调试信息
            
            # 构建 INSERT 语句
            columns = ', '.join([f'"{k}"' for k in data.keys()])
            placeholders = ', '.join(['?' for _ in data])
            sql = f'INSERT INTO "default_table" ({columns}) VALUES ({placeholders})'
            values = tuple(data.values())
            
            print("执行的SQL:", sql)  # 调试信息
            print("参数值:", values)  # 调试信息
            
            # 执行插入操作
            cursor = self.connection.cursor()
            cursor.execute(sql, values)
            self.connection.commit()
            
            print("插入成功，最后插入的ID:", cursor.lastrowid)  # 调试信息
            return [{"status": "success", "data": {"id": cursor.lastrowid}}]
            
        except Exception as e:
            print(f"创建合同时出错: {str(e)}")  # 调试信息
            if hasattr(self, 'connection'):
                self.connection.rollback()
            raise

    def get_contract(self, contract_id):
        """获取单个合同信息"""
        sql = f"SELECT * FROM default_table WHERE id = {contract_id}"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success' and result[0]['data']:
            return result[0]['data'][0]
        return None

    def get_all_contracts(self):
        """获取所有合同列表"""
        sql = "SELECT * FROM default_table ORDER BY id DESC"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []

    def update_contract_status(self, contract_id, status):
        """更新合同状态"""
        sql = f"UPDATE default_table SET status = '{status}' WHERE id = {contract_id}"
        return self.db.send_to_server([sql])

    def delete_contract(self, contract_id):
        """删除合同（标记为已删除）"""
        sql = f"UPDATE default_table SET status = 'deleted' WHERE id = {contract_id}"
        return self.db.send_to_server([sql])

    def get_contracts_by_date_range(self, start_date, end_date):
        """按日期范围查询合同"""
        sql = f"SELECT * FROM default_table WHERE date BETWEEN '{start_date}' AND '{end_date}' ORDER BY date DESC"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []

    def get_contracts_by_company(self, company_name):
        """按供应商公司查询合同"""
        sql = f"SELECT * FROM default_table WHERE ven_name LIKE '%{company_name}%'"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []

    def get_contracts_by_type(self, cate_type):
        """按类别（对公/对私）查询合同"""
        sql = f"SELECT * FROM default_table WHERE cate = '{cate_type}'"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []

    def update_contract(self, contract_id, update_data):
        """更新合同信息
        update_data: 字典，包含需要更新的字段和值
        """
        set_clause = ", ".join([f"{key} = ?" for key in update_data.keys()])
        query = f"UPDATE default_table SET {set_clause} WHERE id = ?"
        
        values = list(update_data.values())
        values.append(contract_id)
        return self.db.send_to_server([query] + values)

    def get_total_amount_by_company(self, company_name):
        """获取某个供应商的合同总金额"""
        sql = f"SELECT SUM(money) as total FROM default_table WHERE ven_name = '{company_name}'"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success' and result[0]['data']:
            return result[0]['data'][0]['total']
        return 0

    def get_contracts_by_item(self, item_type):
        """按供应物品类型查询合同"""
        sql = f"SELECT * FROM default_table WHERE item_sup LIKE '%{item_type}%'"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []

    def create_contract_file(self, file_data):
        """创建合同文件记录"""
        sql = f"""
        INSERT INTO contract_files (contract_id, filename, file_type, is_converted)
        VALUES ({file_data[0]}, '{file_data[1]}', '{file_data[2]}', {file_data[3]})
        """
        return self.db.send_to_server([sql])

    def get_contract_files(self, contract_id):
        """获取合同相关的所有文件"""
        sql = f"SELECT * FROM contract_files WHERE contract_id = {contract_id}"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []

    def create_approval_record(self, approval_data):
        """创建审批记录"""
        sql = f"""
        INSERT INTO approval_records (contract_id, approver, status, comment, created_at)
        VALUES ({approval_data[0]}, '{approval_data[1]}', '{approval_data[2]}', '{approval_data[3]}', '{str(datetime.now())}')
        """
        return self.db.send_to_server([sql])

    def get_approval_records(self, contract_id):
        """获取合同的审批记录"""
        sql = f"SELECT * FROM approval_records WHERE contract_id = {contract_id} ORDER BY created_at DESC"
        result = self.db.send_to_server([sql])
        if result and result[0]['status'] == 'success':
            return result[0]['data']
        return []
